const db = require("../config/mysql");
const { v4: uuidv4 } = require("uuid");

class GoodsModel {
  constructor() {
    this.db = db;
    // goods 所有字段
    this.goodsFields = [
      "goods_id",
      "code",
      "name",
      "goods_pic",
      "cate_id",
      "specification",
      "unit",
      "supplier_id",
      "status",
      "in_price",
      "out_price",
      "safety_stock",
      "note",
      "created_at",
      "updated_at",
    ]
      .map((f) => `g.${f}`)
      .join(", ");
  }

  // 插入食材，并回写自动生成的编码
  async addGoodsWithCodeTransaction(data, cateAcronym) {
    let connection;
    try {
      // 从连接池获取连接
      connection = await this.db.getConnection();
      await connection.beginTransaction();

      // 临时占位符，确保它在第一次 INSERT 时是唯一的非 NULL 值
      const tempCode = uuidv4();

      // 第一次插入 INSERT (Code 暂为占位符)
      const insertSql = `
        INSERT INTO goods (code, name, cate_id, specification, unit, supplier_id, status, in_price, out_price, safety_stock, note) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

      const specValue = data.specification || null;
      const supplierValue = data.supplier_id || null;
      const safetyStockValue = data.safety_stock || 0;

      const insertParams = [
        tempCode, // 传入临时 Code
        data.name,
        data.cate_id,
        specValue,
        data.unit,
        supplierValue,
        data.status || 1,
        data.in_price || 0.0,
        data.out_price || 0.0,
        safetyStockValue,
        data.note || null,
      ];
      const [insertResult] = await connection.query(insertSql, insertParams);
      const insertId = insertResult.insertId;

      // 生成最终 Code
      const CODE_PREFIX = "FD";
      const idString = String(insertId).padStart(3, "0");
      const finalCode = `${CODE_PREFIX}${cateAcronym}${idString}`;

      // 第二次更新 UPDATE (回写 Code)
      const updateSql = `UPDATE goods SET code = ? WHERE goods_id = ?`;
      await connection.query(updateSql, [finalCode, insertId]);

      await connection.commit();

      return { insertId, finalCode };
    } catch (error) {
      if (connection) {
        await connection.rollback(); // 出现错误则回滚
      }

      // 捕获数据库层面的唯一性错误
      if (error.code === "ER_DUP_ENTRY") {
        const field = error.message.includes("uk_goods_name") ? "名称" : "编号";
        throw new Error(`食材${field}已存在`);
      }

      console.error("事务失败:", error.message);
      throw new Error("新增食材失败 (事务回滚)");
    } finally {
      if (connection) {
        connection.release(); // 释放连接
      }
    }
  }

  // 获取食材列表 (搜索、过滤、分页)
  async getAllGoods({
    page = 1,
    pageSize = 10,
    keyword = "", // 搜索关键词 (名称或编号)
    status = 1, // 状态: 1-启用, 0-停用 (默认只查启用)
    cateId = null, // 分类ID
    supplierId = null, // 默认供应商ID
  }) {
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    let conditions = "WHERE 1=1 ";
    const params = [];

    if (status !== null && status !== undefined) {
      conditions += "AND g.status = ? ";
      params.push(parseInt(status));
    }
    if (cateId) {
      conditions += "AND g.cate_id = ? ";
      params.push(parseInt(cateId));
    }
    if (supplierId) {
      conditions += "AND g.supplier_id = ? ";
      params.push(parseInt(supplierId));
    }
    if (keyword) {
      conditions += "AND (g.name LIKE ? OR g.code LIKE ?) ";
      params.push(`%${keyword}%`, `%${keyword}%`);
    }

    const totalSql = `SELECT COUNT(*) AS total FROM goods g ${conditions}`;
    const [totalResult] = await this.db.query(totalSql, params);
    const total = totalResult[0].total;

    let dataSql = `
    SELECT ${this.goodsFields}, c.name AS cate_name, s.supplier_name,
           IFNULL(TI.total_stock, 0.0) AS current_stock, SS.stock_alert_min, SS.stock_alert_max  
    FROM goods g
    LEFT JOIN category c ON g.cate_id = c.cate_id
    LEFT JOIN supplier s ON g.supplier_id = s.supplier_id
    LEFT JOIN (
        SELECT 
            goods_id, 
            SUM(current_stock) AS total_stock 
        FROM inventory 
        GROUP BY goods_id
    ) AS TI ON g.goods_id = TI.goods_id 
    LEFT JOIN system_setting SS ON SS.id = 1
    ${conditions}
    ORDER BY g.goods_id DESC 
    LIMIT ? OFFSET ?`;

    const listParams = params.concat(limit, offset);

    try {
      const [dataResult] = await this.db.query(dataSql, listParams);
      return { total, data: dataResult, page, pageSize };
    } catch (error) {
      console.error(error.message);
      throw new Error("无法从数据库中获取食材列表");
    }
  }

  // 根据ID获取单个食材详情
  async getGoodsById(goodsId) {
    const sql = `
    SELECT ${this.goodsFields}, c.name AS cate_name, s.supplier_name,
           IFNULL(TI.total_stock, 0.0) AS current_stock,SS.stock_alert_min, SS.stock_alert_max
    FROM goods g
    LEFT JOIN category c ON g.cate_id = c.cate_id
    LEFT JOIN supplier s ON g.supplier_id = s.supplier_id
    LEFT JOIN (
        SELECT 
            goods_id, 
            SUM(current_stock) AS total_stock 
        FROM inventory 
        GROUP BY goods_id
    ) AS TI ON g.goods_id = TI.goods_id 
    LEFT JOIN system_setting SS ON SS.id = 1
    WHERE g.goods_id = ?`;

    try {
      const [result] = await this.db.query(sql, [goodsId]);
      return result[0] || null;
    } catch (error) {
      console.error(error.message);
      throw new Error("无法获取食材详情");
    }
  }
  // 更新食材信息
  async updateGoods(goodsId, data) {
    const updateFields = [];
    const updateValues = [];

    // 强制将空字符串转换成 NULL，以便于数据库存储一致性
    const sanitizedData = {};
    for (const key in data) {
      let value = data[key];
      // 价格/库存字段的数值转换和校验
      if (["in_price", "out_price", "safety_stock"].includes(key)) {
        value = parseFloat(value);
        if (isNaN(value) || value < 0) {
          throw new Error(`${key} 值无效，必须是大于等于零的数字`);
        }
      }
      if (typeof value === "string" && value.trim() === "") {
        value = null;
      }
      sanitizedData[key] = value;
    }

    for (const key in sanitizedData) {
      if (
        Object.hasOwnProperty.call(sanitizedData, key) &&
        key !== "goods_id" &&
        key !== "created_at" &&
        key !== "updated_at"
      ) {
        updateFields.push(`${key} = ?`);
        updateValues.push(sanitizedData[key]);
      }
    }

    if (updateFields.length === 0) {
      return 0;
    }

    const sql = `UPDATE goods SET ${updateFields.join(
      ", "
    )} WHERE goods_id = ?`;
    updateValues.push(goodsId);

    try {
      const [result] = await this.db.query(sql, updateValues);
      return result.affectedRows;
    } catch (error) {
      if (error.code === "ER_DUP_ENTRY") {
        const field = error.message.includes("uk_goods_name") ? "名称" : "编号";
        throw new Error(`更新后的食材${field}已存在`);
      }
      console.error(error.message);
      throw new Error("更新食材信息失败");
    }
  }

  // 检查 食材名称 + 规格 + 默认供应商ID 组合是否已存在 (排除当前 ID)
  async isCompositeKeyExist(
    name,
    specification,
    supplierId,
    inPrice,
    excludeId = null
  ) {
    // 默认值设为 null
    let sql = `
          SELECT COUNT(*) AS count 
          FROM goods 
          WHERE name = ? 
          AND specification <=> ? 
          AND supplier_id <=> ?
          AND in_price = ?`;

    const params = [name, specification || null, supplierId || null, inPrice];

    // 排除当前的 goods.id
    if (excludeId !== null) {
      sql += ` AND goods_id != ?`;
      params.push(excludeId);
    }

    try {
      const [result] = await this.db.query(sql, params);
      return result[0].count > 0;
    } catch (error) {
      console.error(error.message);
      throw new Error("检查食材复合唯一性失败");
    }
  }

  // 检查食材名称或编号是否已存在 (更新时名称校验)
  async isValueExist(type, value, excludeId = null) {
    let sql = `SELECT COUNT(*) AS count FROM goods WHERE ${type} = ?`;
    const params = [value];

    if (excludeId) {
      sql += ` AND goods_id != ?`;
      params.push(excludeId);
    }

    try {
      const [result] = await this.db.query(sql, params);
      return result[0].count > 0;
    } catch (error) {
      console.error(error.message);
      throw new Error(`检查食材${type}唯一性失败`);
    }
  }

  // 软删除 (即停用) 食材
  async deactivateGoods(goodsId) {
    const sql = `UPDATE goods SET status = 0 WHERE goods_id = ? AND status = 1`; // 只有启用中的才能停用

    try {
      const [result] = await this.db.query(sql, [goodsId]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error(error.message);
      throw new Error("食材停用失败");
    }
  }

  // 根据供应商ID获取食材列表
  async getGoodsListBySupplierIds(paramsData) {
    // 获取参数
    const supplierId = paramsData.supplierId;
    const page = parseInt(paramsData.page) || 1;
    const pageSize = parseInt(paramsData.pageSize) || 10;

    // 查询条件
    let conditions = "";
    let countConditions = [];
    let countWhere = "WHERE  1 = 1 ";

    if (supplierId) {
      conditions = `WHERE 1 = 1 AND g.supplier_id in (?) AND g.status = 1`;
      countWhere += `AND g.supplier_id in (?)`;
      countConditions.push(supplierId);
    }
    // 统计食材数量
    const countSql = `SELECT COUNT(*) AS total FROM goods AS g ${conditions}`;
    const [countResult] = await this.db.query(countSql, [supplierId]);
    const total = countResult[0].total;

    // 分页参数
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    countConditions.push(offset, limit);
    countWhere += `AND g.status = 1`;

    // 食材列表+分页 查询
    const sql = `
SELECT ${this.goodsFields}, c.name AS cate_name, s.supplier_name,
   IFNULL(TI.total_stock, 0.0) AS current_stock, SS.stock_alert_min, SS.stock_alert_max
FROM goods g
LEFT JOIN category c ON g.cate_id = c.cate_id
LEFT JOIN supplier s ON g.supplier_id = s.supplier_id
LEFT JOIN (
SELECT 
    goods_id, 
    SUM(current_stock) AS total_stock 
FROM inventory 
GROUP BY goods_id
) AS TI ON g.goods_id = TI.goods_id 
LEFT JOIN system_setting SS ON SS.id = 1
${countWhere}
LIMIT ?,?`;

    try {
      const [result] = await this.db.query(sql, [supplierId, offset, limit]);
      result.forEach((item) => {
        item.current_stock = item.current_stock || 0.0;
        // 采购数量= 当前库存数 / 安全库存数
        // item.quantity= item.current_stock / item.safety_stock;
        item.quantity = 1;
      });
      const data = {
        total: total,
        page: page,
        pageSize: pageSize,
        list: result,
      };
      return data;
    } catch (error) {
      console.error(error.message);
      throw new Error("无法获取食材列表");
    }
  }
}

module.exports = GoodsModel;
